package com.cxtj;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import com.admin.dao.unicode;


@Service("CxtjDAO")
public class CxtjDAO extends JdbcDaoSupport{
	
	@Resource(name = "jdbctemplate219")
	public void setJb(JdbcTemplate jdbctemplate219) {
	 super.setJdbcTemplate(jdbctemplate219);
	    }
	
	@Value("#{configProperties['mgsjTable']}")
	  private String mgsjTable;	
	
	public HashMap<String,Object> selectPage(String page,String keyWord,String userName,String mintime,String maxtime){	
		int pageNo=10;
		HashMap<String,Object> returnMap=new HashMap<String,Object>();		
		StringBuffer sql=new StringBuffer("select * from "+ mgsjTable);
		if(keyWord!=null && !"".equals(keyWord)){
			sql.append(" where consql like '%"+unicode.string2Unicode(keyWord)+"%' ");	
		}else{
			sql.append(" where consql like '%' ");
		}		
		
		if(userName!=null && !"".equals(userName)){
			sql.append(" and sqluser like '%"+userName+"%' ");	 	
		}
		if(mintime!=null && !"".equals(mintime)){
			sql.append(" and czsj > '"+mintime+"' ");	 	
		}
		if(maxtime!=null && !"".equals(maxtime)){
			sql.append(" and czsj < '"+maxtime+"' ");		 	
		}		
		int total=getRecord();   //bug  to do
		if(page!=null && !"".equals(page) && Integer.valueOf(page)>1)
		{	int pages=Integer.valueOf(page);			
	    	if(((pages-1)*pageNo)>total)    //判断页码是否越界
	    		{
	    			page=String.valueOf(total/pageNo<1?0:(total/pageNo)-1);    //若越界则计算并取最大页
	    		}	    	
		}
		if(page!=null && !"".equals(page))
		{
			sql.append(" limit "+(Integer.valueOf(page)-1)*pageNo+", "+pageNo);			
		}else{
			sql.append(" limit 0,10");
		}	

		System.out.println(sql);
		List<CxtjBean> cxList=getJdbcTemplate().query(sql.toString(),new Object[] {}, new BeanPropertyRowMapper<CxtjBean> (CxtjBean.class));		
		if(cxList!=null)
		{	
			returnMap.put("datas", cxList);
			returnMap.put("num", total);
		}
		return returnMap;
	    }
	
	public List<Map<String, Object>> getPieData(){
		String pieSql =  "select sqluser 'name',                         "
				+"			 count(1) 'value'                        "
				+"from "+mgsjTable
				+" GROUP BY sqluser order by count(1) desc        ";		
		System.out.println(pieSql);
		List<Map<String, Object>> pieList = getJdbcTemplate().queryForList(pieSql);
		return pieList;		
	}
	
	
	
	public List<Map<String, Object>> getBarData(){	
		int limitNum = 5;
		String dateSql = "select DISTINCT DATE_FORMAT(czsj,'%y%m%d') days "       
						+" from "  + mgsjTable
						+" order by  days  limit 0,"+limitNum;
		System.out.println(dateSql);
		List<Map<String, Object>> dateLis = getJdbcTemplate().queryForList(dateSql);
		
		String barSql =	 " SELECT sqluser ";
		for(Map<String, Object> date:dateLis)
		{
			barSql = barSql + ", max(case days when '"+date.get("days")+"' then tjcs else 0 end) 	'"+date.get("days")+"' ";
		}

		barSql = barSql
				+" from (select days,                                                "
				+" 					sqluser ,                                               "
				+" 					count(1) tjcs                                           "
				+" 			from (                                                      "
				+" 						select DATE_FORMAT(czsj,'%y%m%d') days,sqluser           "
				+" 						from    "+mgsjTable
				+" 			)t	GROUP BY days,sqluser                                    "
				+" 	) b group by sqluser order by days desc LIMIT 0,3             ";
		
		System.out.println(barSql);
		List<Map<String, Object>> barList = getJdbcTemplate().queryForList(barSql);
		return barList;		
	}
	
	private  int getRecord() {				
		String sql="select count(1) from "+mgsjTable;
		return this.getJdbcTemplate().queryForInt(sql);
		
	}
	
	public  Map<String,Object> getMaxDate() {				
		String sql="select max(czsj)  maxDate  from "+mgsjTable;
		return this.getJdbcTemplate().queryForMap(sql);		
	}
	
	public  List<Map<String, Object>> getMaxDateList(List<String> tableList) {				
		String sql="select OBJ_NAME tablename,max(czsj) maxdate  from "+mgsjTable;
		if(tableList!=null && tableList.size()>0)
		{	
			sql=sql+" where OBJ_NAME in( '' ";
			for(String table:tableList){
				sql=sql+" , '"+table+"' ";
			}
			sql=sql+" ) ";
		}
		sql=sql+" GROUP BY OBJ_NAME ";
		return this.getJdbcTemplate().queryForList(sql);		
	}
	
	public List<Map<String, Object>> getPieDataTb(){
		String pieSql =  "select OBJ_NAME 'name',                         "
				+"			 count(1) 'value'                        "
				+"from "+mgsjTable
				+" GROUP BY OBJ_NAME order by count(1) desc        ";		
		System.out.println(pieSql);
		List<Map<String, Object>> pieList = getJdbcTemplate().queryForList(pieSql);
		return pieList;		
	}
	
	
	
	public List<Map<String, Object>> getBarDataTb(){		
		int limitNum = 5;
		String dateSql = "select DISTINCT DATE_FORMAT(czsj,'%y%m%d') days "       
						+" from "  + mgsjTable
						+" order by  days  limit 0,"+limitNum;
		System.out.println(dateSql);
		List<Map<String, Object>> dateLis = getJdbcTemplate().queryForList(dateSql);
		
		String barSql =	 " SELECT OBJ_NAME ";
		for(Map<String, Object> date:dateLis)
		{
			barSql = barSql + ", max(case days when '"+date.get("days")+"' then tjcs else 0 end) 	'"+date.get("days")+"' ";
		}
		barSql = barSql
				+" from (select days,                                                "
				+" 					OBJ_NAME ,                                               "
				+" 					count(1) tjcs                                           "
				+" 			from (                                                      "
				+" 						select DATE_FORMAT(czsj,'%y%m%d') days,OBJ_NAME           "
				+" 						from    "+mgsjTable
				+" 			)t	GROUP BY days,OBJ_NAME                                    "
				+" 	) b group by OBJ_NAME order by days desc LIMIT 0,3             ";
		
		System.out.println(barSql);
		List<Map<String, Object>> barList = getJdbcTemplate().queryForList(barSql);
		return barList;		
	}
	
	public List<EtlBean> getETL(List<EtlBean> inEtl){		
		int limitNum = 5;
		StringBuffer sql = new StringBuffer("select sqluser user_name,OBJ_NAME table_name,max(czsj)  data_time,count(1) data_count from "+mgsjTable +"  where OBJ_NAME in ( ");
		if(inEtl.size()>0){
			sql.append(" '"+inEtl.get(0).getTable_name()+"' ");
			if(inEtl.size()>1){
				for(int i=1;i<inEtl.size();i++){
					sql.append(",'"+inEtl.get(i).getTable_name()+"' ");
				}				
			}
		}
		sql.append(" )  group by  sqluser,OBJ_NAME ");
		System.out.println(this.getClass()+":"+sql);
		List<EtlBean> dataLis = getJdbcTemplate().query(sql.toString(),new Object[] {}, new BeanPropertyRowMapper<EtlBean> (EtlBean.class));		
		return dataLis;		
	}
	
	
}
